package com.api.gbhg.huangwm.cron;

import com.api.gbhg.huangwm.dao.BudgetManagementDownDao;
import com.api.gbhg.huangwm.dao.ProjectOperationDao;
import com.api.gbhg.huangwm.dao.ReimbursementListDao;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.general.BaseBean;
import weaver.general.GCONST;
import weaver.general.Util;
import weaver.interfaces.schedule.BaseCronJob;

import java.util.ArrayList;
import java.util.List;

/**
 * @Author Huangwm
 * @Date 2022-10-14
 * @Version 1.0
 * @Description TODO 更新项目经济分类冻结和实际报销额度  以及预算来源冻结和实际报销额度 执行版
 **/
public class UpdateProjectActive extends BaseCronJob {

    public String ysnd;

    public String getYsnd() {
        return ysnd;
    }

    public void setYsnd(String ysnd) {
        this.ysnd = ysnd;
    }

    @Override
    public void execute() {
        runAfter2();
        Jingfeilaiyuan();
        Jingjifeilei();
    }

    public  void Jingjifeilei(){
        String sql = "select * from uf_jingfei where lcjlid IN(SELECT lcjlsqjlid FROM dbo.uf_jingfei_version WHERE ysnd="+ysnd+")";
        RecordSet get1 = new RecordSet();
        RecordSet get2 = new RecordSet();
        RecordSetTrans rst = new RecordSetTrans();
        rst.setAutoCommit(false);
        boolean r = true;
        RecordSet rs = new RecordSet();
        rs.execute(sql);
        while (rs.next()){
            String requestId = rs.getString("lcjlid");
            String bmjjfl = rs.getString("bmzcjjfl");
            String getYbx = "SELECT ISNULL(SUM(d1.tybxje-isnull(d1.cxje,0)),0) as ybx FROM dbo.uf_bxjl_kj_dt1 d1 \n" +
                    "LEFT JOIN uf_bxjl_kj d ON d1.lcid=d.lcid\n" +
                    "LEFT JOIN dbo.workflow_requestbase wr ON d1.lcid=wr.requestid\n" +
                    "WHERE ysxm="+requestId+" AND wr.currentnodetype=3 and bmjjzcfl="+bmjjfl;
            String getDj = "SELECT ISNULL(SUM(t.sqbxje-isnull(cxje,0)),0) as dj FROM (\n" +
                    " SELECT wr.requestid,jl.lclx,jl.sqbxje,jl.cxje FROM dbo.uf_bxsjjl  jl LEFT JOIN workflow_requestbase wr ON jl.lcid=wr.requestid\n" +
                    " WHERE ysxm="+requestId+" AND wr.currentnodetype in(1,2) and bmjjfl="+bmjjfl+
                    " )t WHERE t.requestid IS NOT NULL";

            get1.execute(getYbx);
            get1.next();
            Double ybx = get1.getDouble("ybx");

            get2.execute(getDj);
            get2.next();
            Double dj = get2.getDouble("dj");

            try{
                UpdateJingjifeilei(rst,requestId,bmjjfl,ybx,dj);
            }catch (Exception e){
                r = false;
                e.printStackTrace();
                break;
            }
        }
        if(!r){
            rst.rollback();
        }else {
            rst.commit();
            System.err.println("jingfei表更新完成");
        }
    }

    /**
     * 更新jingfei表已报销和冻结
     * @param rst
     * @param requestId
     * @param bmjjfl
     * @param ybx
     * @param dj
     */
    public  void UpdateJingjifeilei(RecordSetTrans rst,String requestId,String bmjjfl,Double ybx,Double dj) throws Exception {
        String sql = "update uf_jingfei set ybx="+ybx+",dj="+dj+",syed=ze-"+ybx+"-("+dj + ") where lcjlid="+requestId+" and bmzcjjfl="+bmjjfl;
        boolean result = rst.executeUpdate(sql);
        System.err.println(sql);
        System.err.println("项目id="+requestId+" 经济分类="+bmjjfl+"  已报销="+ybx+" 冻结="+dj+" 更新结果="+result);
    }

    public void Jingfeilaiyuan(){
        //String sql = "select * from uf_jingfeilaiyuan where lcid not in(593196,1300446)";
        String sql = "select * from uf_jingfeilaiyuan where  lcid  IN(SELECT lcjlsqjlid FROM dbo.uf_jingfei_version WHERE ysnd="+ysnd+")";
        RecordSet get1 = new RecordSet();
        RecordSet get2 = new RecordSet();
        RecordSetTrans rst = new RecordSetTrans();
        rst.setAutoCommit(false);
        boolean r = true;
        RecordSet rs1= new RecordSet();
        RecordSet rs2 = new RecordSet();
        rs1.execute(sql);
        BudgetManagementDownDao downDao = new BudgetManagementDownDao();
        while (rs1.next()){
            String requestId = rs1.getString("lcid");
            // 当年预算 上年结转
            String yslylx = rs1.getString("yslylx");
            String srlx = rs1.getString("srlx");//0 财政拨款 1非财政
            String yssrfl = rs1.getString("zcqd");
            String dfczbkxm = rs1.getString("dfczbkxm");
            String zyczbksr = rs1.getString("zyczbksr");
            System.err.print("项目id="+requestId+" 预算来源="+yslylx+" srlx="+srlx+" zcqd="+yssrfl+" dfczbkxm="+dfczbkxm+" zyczbksr="+zyczbksr);
            if(!srlx.isEmpty()){
                //来源实际报销金额
                Double sjbxje = downDao.getSjbxje(rs2,requestId,yslylx,srlx,yssrfl,dfczbkxm,zyczbksr);
                Double djje = getDjbxje(rs2,requestId,yslylx,srlx,yssrfl,dfczbkxm,zyczbksr);
                System.err.println(" 渠道冻结金额="+djje+" 实际报销金额="+sjbxje+" 当前剩余额度="+rs1.getString("syed"));
                try{
                    UpdateJingfeilaiyuan(rst,requestId,yslylx,srlx,yssrfl,dfczbkxm,zyczbksr,sjbxje,djje);
                }catch (Exception e){
                    r = false;
                    e.printStackTrace();
                    break;
                }
            }else{
                System.err.println(" 类型为空");
            }
        }
        if(!r){
            rst.rollback();
        }else {
            rst.commit();
            System.err.println("jingfeilaiyuan表更新完成");
        }
    }
    /**
     * 更新预算来源的冻结和实际报销金额
     * @param xmid 项目id
     * @param yslylx 预算来源（当年 or 上年）
     * @param srlx  收入类型（财政 or 非财政）
     * @param srfl 非中央收入发呢类
     * @param dfczbkxm 地方财政拨款项目id
     * @param zyczbksr  中央财政核批id
     * @param sjbxje  实际报销金额
     */
    public void UpdateJingfeilaiyuan(RecordSetTrans rst,String xmid,String yslylx,String srlx,String srfl,String dfczbkxm,String zyczbksr,Double sjbxje,Double djje) throws Exception {
        String baseSql = "update uf_jingfeilaiyuan set djje="+djje+",syed=je-("+djje+")-("+sjbxje+") "+
                "WHERE lcid="+xmid+" and yslylx='"+yslylx+"' and srlx="+srlx+" \n";
        if(Integer.parseInt(srlx)==0){
            //财政拨款
            baseSql += " and zyczbksr='"+zyczbksr+"' ";
        }else{
            if(srfl.equals("5") || srfl.equals("8") ){
                baseSql += " and zcqd="+srfl+" and dfczbkxm="+dfczbkxm;
            }else{
                baseSql += " and zcqd="+srfl;
            }
        }
        //System.err.println(baseSql);
        boolean result = rst.executeUpdate(baseSql);
        System.err.println("项目id="+xmid+" 预算来源="+yslylx+"  来源类型="+srlx+" 收入分类="+srfl+" 财政拨款="+zyczbksr+" 更新结果="+result);
    }

    /**
     * 查询项目渠道冻结
     * @param xmid 项目id
     * @param yslylx 预算来源（当年 or 上年）
     * @param srlx  收入类型（财政 or 非财政）
     * @param srfl 非中央收入发呢类
     * @param dfczbkxm 地方财政拨款项目id
     * @param zyczbksr  中央财政核批id
     * @return
     */
    public Double getDjbxje(RecordSet rs,String xmid,String yslylx,String srlx,String srfl,String dfczbkxm,String zyczbksr){
        String baseSql = "SELECT ISNULL(SUM(d1.tybxje-isnull(d1.cxje,0)),0) AS sjbxje FROM dbo.uf_bxjl_kj_dt1 d1 \n" +
                "LEFT JOIN uf_bxjl_kj d ON d1.lcid=d.lcid\n" +
                "LEFT JOIN dbo.workflow_requestbase wr ON d1.lcid=wr.requestid\n" +
                "WHERE  d.shrq IS NULL and wr.currentnodetype in(1,2) AND ysxm="+xmid+" and ysly="+(yslylx.startsWith("当年")?"0":"1")+" and zcqd="+srlx+" \n";
        if(Integer.parseInt(srlx)==0){
            //财政拨款
            String kmj = new BaseBean().getPropValue("Verification","yj");
            if(zyczbksr.split("_")[0].equals(kmj)){
                baseSql += " and kmjxj=0 and dygnkmbid="+zyczbksr.split("_")[1];
            }else{
                baseSql += " and kmjxj=1 and dygnkmbid="+zyczbksr.split("_")[1];
            }
        }else{
            if(srfl.equals("5") || srfl.equals("8") ){
                baseSql += " and fzysrfl="+srfl+" and zcdfczbkxm="+dfczbkxm;
            }else {
                baseSql += " and fzysrfl=" + srfl ;
            }
        }
        rs.execute(baseSql);
        if(rs.next()){
            return rs.getDouble("sjbxje");
        }
        return 0.00;
    }

    /**
     * 同步各流程费用报销明细中的支出渠道到uf_bxjl_kj_dt1
     */
    public void runAfter2(){
        ProjectOperationDao dao = new ProjectOperationDao();
        RecordSet rs1 = new RecordSet();
        RecordSet rs2 = new RecordSet();
        RecordSet rs3 = new RecordSet();
        RecordSet rs4 = new RecordSet();
        List<List> datas = new ArrayList<>();
        rs1.execute(" select id,lcid,mxid from uf_bxjl_kj_dt1 where ysxm IN(SELECT lcjlsqjlid FROM dbo.uf_jingfei_version WHERE ysnd="+ysnd+") ");
        while (rs1.next()){
            List data = new ArrayList();
            String dtid = rs1.getString("id");
            String requestid = rs1.getString("lcid");
            System.err.println("requestid="+requestid);
            String tablename = dao.getWorkflowTabNameByRequestid(rs2,requestid);
            String sql = "select dt.id,ysly,xzkmdm,kmdm,kmjxj,hpssjid,fzysrfl,zcdfczbkxm,isnull(cxje,0) as cxje \n" +
                    "from "+tablename+"_dt1 dt\n" +
                    "left join uf_gxmdygnkm on uf_gxmdygnkm.id=dt.xzkmdm\n" +
                    "left join uf_srysfl on dt.fzysrfl=uf_srysfl.id\n" +
                    "left join uf_fzysrxm on dt.zcdfczbkxm=uf_fzysrxm.id\n" +
                    "where dt.id="+rs1.getString("mxid");
            if(rs3.execute(sql)){
                rs3.next();
                String ysly = Util.null2String(rs3.getString("ysly"));//预算来源 0-财政拨款 1-非财政
                String xzkmdm = Util.null2String(rs3.getString("xzkmdm"));//选择科目代码 关联uf_gxmdygnkm表id
                String kmdm = Util.null2String(rs3.getString("kmdm"));//财政拨款科目代码 关联uf_gxmdygnkm表
                String kmjxj = Util.null2String(rs3.getString("kmjxj"));//科目级下级 0-科目级 1-科目下级
                String zyczbkid = Util.null2String(rs3.getString("hpssjid")).equals("")?"":rs3.getString("hpssjid").split("_")[1];//核批的财政收入表id
                String fzysrfl = Util.null2String(rs3.getString("fzysrfl"));//非中央收入分类
                String zcdfczbkxm = Util.null2String(rs3.getString("zcdfczbkxm"));//地方财政拨款项目
                Double cxje =  rs3.getDouble("cxje");//冲销金额
                data.add(kmjxj);
                data.add(zyczbkid);
                data.add(fzysrfl);
                data.add(zcdfczbkxm);
                data.add(cxje);
                data.add(dtid);
                datas.add(data);
            }else{
                sql = "select dt.id,ysly,xzkmdm,kmdm,kmjxj,dygnkmbid,fzysrfl,zcdfczbkxm,isnull(bccxjkje,0) as bccxjkje,hpssjid \n" +
                        "from "+tablename+" dt\n" +
                        "left join uf_gxmdygnkm on uf_gxmdygnkm.id=dt.xzkmdm\n" +
                        "left join uf_srysfl on dt.fzysrfl=uf_srysfl.id\n" +
                        "left join uf_fzysrxm on dt.zcdfczbkxm=uf_fzysrxm.id\n" +
                        "where dt.id="+rs1.getString("mxid");
                if(rs3.execute(sql) && rs3.next()) {
                    String ysly = Util.null2String(rs3.getString("ysly"));//预算来源 0-财政拨款 1-非财政
                    String xzkmdm = Util.null2String(rs3.getString("xzkmdm"));//选择科目代码 关联uf_gxmdygnkm表id
                    String kmdm = Util.null2String(rs3.getString("kmdm"));//财政拨款科目代码 关联uf_gxmdygnkm表
                    String kmjxj = Util.null2String(rs3.getString("kmjxj"));//科目级下级 0-科目级 1-科目下级
                    String zyczbkid = Util.null2String(rs3.getString("hpssjid")).equals("")?"":rs3.getString("hpssjid").split("_")[1];//核批的财政收入表id
                    String fzysrfl = Util.null2String(rs3.getString("fzysrfl"));//非中央收入分类
                    String zcdfczbkxm = Util.null2String(rs3.getString("zcdfczbkxm"));//地方财政拨款项目
                    Double bccxjkje = rs3.getDouble("bccxjkje");//冲销金额
                    String hpssjid = Util.null2String(rs3.getString("hpssjid"));//地方财政拨款项目
                    data.add(kmjxj);
                    data.add(zyczbkid);
                    data.add(fzysrfl);
                    data.add(zcdfczbkxm);
                    data.add(bccxjkje);
                    data.add(dtid);
                    datas.add(data);
                }
            }
        }

        try{
            String sql = "update uf_bxjl_kj_dt1 set kmjxj=?,dygnkmbid=?,fzysrfl=?,zcdfczbkxm=?,cxje=? where id=? ";
            boolean r = rs4.executeBatchSql(sql,datas);
            System.err.println("插入数据结果 "+r);
            new RecordSet().execute("update uf_bxjl_kj_dt1 set dw=uf_jingfei_version.dlhsdw from uf_jingfei_version where uf_bxjl_kj_dt1.ysxm=uf_jingfei_version.lcjlsqjlid");
            new RecordSet().execute("UPDATE uf_bxjl_kj_dt1 SET dlhsdw=uf_jingfei_version.dlhsdw FROM dbo.uf_jingfei_version WHERE lcjlsqjlid=ysxm");
        }catch (Exception e){
            System.err.println("插入数据SQL异常");
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
    }

}
